import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author sky
 * @create 2019-07-04 13:51
 **/
public class FileOperation {

    /*
     *
     * @Title 读取xls
     * @author sky
     * @creat_date 2019/7/5 8:47
     * @param filePath xls文件路径
     * @param sheetNum sheet的页标
     * @param startRow 开始的行数
     * @param endRow 结束的行数
     * @rampa selectColumn需要的列的数组
     * @return  List<List<String>>     里面的list代表一行
     */
    public List rerdXls(String filePath, int sheetNum, int startRow, int endRow, int[] selectColumn) {
        List<List<String>> list = new ArrayList<List<String>>();
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(new File(filePath));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        Sheet[] sheet = workbook.getSheets();
        Sheet s = sheet[sheetNum];
        // 得到行的总数
        int rowCount = s.getRows();
        //读取的最后一行
        int row = 0;
        if (endRow > rowCount) {
            System.out.println("输入结束行数大于实际行数，赋值为最大长度");
            row = rowCount;
        } else if (endRow == 0) {
            row = rowCount;
        } else {
            row = endRow;
        }

        //读取xls
        for (int i = startRow; i < row; i++) {
            List list2 = new ArrayList<String>();
            for (int sl : selectColumn) {
                list2.add(s.getCell(sl, i).getContents());
            }
            list.add(list2);
        }
        return list;
    }

    /*
     *
     * @Title 读取xlsx
     * @author sky
     * @creat_date 2019/7/5 8:47
     * @param filePath xls文件路径
     * @param sheetNum sheet的页标
     * @param startRow 开始的行数
     * @param endRow 结束的行数
     * @rampa selectColumn需要的列的数组
     * @return  List<List<String>>     里面的list代表一行
     */
    public List rerdXlsx(String filePath, int sheetNum, int startRow, int endRow, int[] selectColumn) {
        List list = new ArrayList<List<String>>();
        // 创建工作簿
        XSSFWorkbook workBook = null;
        try {
            workBook = new XSSFWorkbook(new File(filePath));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        XSSFSheet sheet = workBook.getSheetAt(sheetNum);
        // 得到行的总数
        int rowCount = sheet.getLastRowNum();
        System.out.println("长度："+rowCount);
        //读取的最后一行
        int row = 0;
        if (endRow > rowCount) {
            System.out.println("输入结束行数大于实际行数，赋值为最大长度");
            row = rowCount;
        } else if (endRow == 0) {
            row = rowCount;
        } else {
            row = endRow;
        }

        //读取xlsx
        for (int i = startRow; i <= row; i++) {
            XSSFRow rowDetail = sheet.getRow(i);
            // 行数检查
            if (rowDetail.getPhysicalNumberOfCells() < 1) {
                System.out.println("xlsx列数少于1！！！！！数据有问题");
                return list;
            }
            List list2 = new ArrayList<String>();
            for (int sl : selectColumn) {
                //System.out.println(rowDetail.getCell(sl).toString());
                list2.add(rowDetail.getCell(sl).toString());
            }
            list.add(list2);
        }

        return list;
    }


    /*
     *
     * @Title 读取csv
     * @author sky
     * @creat_date 2019/7/5 8:47
     * @param filePath xls文件路径
     * @param startRow 开始的行数
     * @param endRow 结束的行数
     * @rampa selectColumn需要的列的数组
     * @return  List<List<String>>     里面的list代表一行
     */
    public List rerdCsv(String filePath, int startRow, int endRow, int[] selectColumn) {
        //CSV默认多了一行
        startRow-=1;
        List list = new ArrayList<List<String>>();
        //暂存csv的list
        List<String> tempList = new ArrayList<String>();
        BufferedReader bufferedreader = null;
        try {
            //解决中文乱码
            InputStreamReader isr = new InputStreamReader(new FileInputStream(new File(filePath)), "GBK");
            bufferedreader = new BufferedReader(isr);
            String stemp ;
            while ((stemp = bufferedreader.readLine()) != null) {
                tempList.add(stemp);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }   catch (IOException e){
            e.printStackTrace();
        }

        // 得到行的总数
        int rowCount = tempList.size();
       // System.out.println("长度："+rowCount);
        //读取的最后一行
        int row = 0;
        if (endRow > rowCount) {
            System.out.println("输入结束行数大于实际行数，赋值为最大长度");
            row = rowCount;
        } else if (endRow == 0) {
            row = rowCount;
        } else {
            row = endRow;
        }

        //读取csv
        for (int i = startRow; i < row; i++) {
          List list2 = new ArrayList<String>();
            for (int sl : selectColumn) {
                System.out.println(tempList.get(i).split(",")[sl]);
               list2.add(tempList.get(i).split(",")[sl].replace("\"", ""));
            }
            list.add(list2);
        }

        return list;
    }

    /*
     * @Title 根据母SQL替换占位符生成子SQL
     * @author sky
     * @creat_date 2019/7/5 9:32
     * @param list 读取源数据生成的list
     * @param sqlRoot 带占位符的母SQL
     * @param selectColumn需要列的数组
     * @return  组合成的sql String
     */
    public String creatSQL(List<List<String>> list, String sqlRoot, int[] selectColumn){
        StringBuffer strB = new StringBuffer();
        for(int i = 0;i < list.size(); i ++) {
            // Map<String, String> mapstring = new HashMap<>();
            String sqlSon = sqlRoot;
            List<String> listSon = (List) list.get(i);
            for (int j=1;j<=selectColumn.length;j++){
               //替换占位符
                sqlSon = sqlSon.replace("${" + j + "}$", listSon.get(j-1));
            }
            strB.append(sqlSon);
            strB.append(";");
        }
        return strB.toString();
    }




    /*
     *
     * @Title   写入到txt文件
     * @author sky
     * @creat_date 2019/7/5 8:45
     * @param  sql 要写入文件的String
     *  @param filePath  文件路径
     * @return void
     */
    public void writeFile(String sql,String filePath) throws IOException {
        // 相对路径，如果没有则要建立一个新的txt文件
        File writeName = new File(filePath);
        // 创建新文件,有同名的文件的话直接覆盖
        writeName.createNewFile();
        try (FileWriter writer = new FileWriter(writeName); BufferedWriter out = new BufferedWriter(writer)) {
            out.write(sql);
            // 把缓存区内容压入文件
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
